﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using Home.Utils;
using NhanAi.Entity;

namespace NhanAi.DAO
{
    public class CommentDao:DBConnection
    {
        public int Count(bool unapproved)
        {
            string commandText = "";
            if (unapproved)
            {
                commandText = "select count(*) from HmComment where Approved=0";
            }
            else
            {
                commandText = "select count(*) from HmComment";
            }
            OleDbCommand cmd = new OleDbCommand(commandText,conn);
            if (Connect())
            {
                int count = 0;
                count=(int)cmd.ExecuteScalar();
                Disconnect();
                return count;
            }
            return 0;
        }
        public void Create(Comment c)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmComment");
            if (Connect())
            {
                try
                {
                    nb.AddValueField("PostId", c.PostId);
                    nb.AddValueField("Author", c.Author);
                    nb.AddValueField("AuthorEmail", c.AuthorEmail);
                    nb.AddValueField("CommentDate", c.CommentDate.ToString("MM/dd/yyyy hh:mm:ss"));
                    nb.AddValueField("CommentContent", c.CommentContent);
                    nb.AddValueField("CommentType", c.CommentType);
                    nb.AddValueField("Approved", c.Approved);
                    nb.AddValueField("ParentId", c.ParentId);
                    nb.AddValueField("UserId", c.UserId);

                    OleDbCommand cmd = nb.GetCreateCommand(conn);

                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "select @@IDENTITY";
                    c.Id = (int)cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                Disconnect();
            }

        }
        public void Update(Comment c)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmComment");
            if (Connect())
            {

                //nb.AddValueField("ID",u.Id);
                nb.AddValueField("PostId", c.PostId);
                nb.AddValueField("Author", c.Author);
                nb.AddValueField("AuthorEmail", c.AuthorEmail);
                nb.AddValueField("CommentDate", c.CommentDate.ToString("MM/dd/yyyy hh:mm:ss"));
                nb.AddValueField("CommentContent", c.CommentContent);
                nb.AddValueField("CommentType", c.CommentType);
                nb.AddValueField("Approved", c.Approved);
                nb.AddValueField("ParentId", c.ParentId);
                //nb.AddValueField("UserId", c.UserId);
                nb.AddConditionField("ID", c.Id);
                OleDbCommand cmd = nb.GetUpdateCommand(conn, true);
                cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        public void Delele(int uid)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmComment");
            if (Connect())
            {

                nb.AddConditionField("ID", uid);
                OleDbCommand cmd = nb.GetDeleteCommand(conn, true);
                cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        public void Delete(string ids)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmComment");
            OleDbCommand cmd = new OleDbCommand("delete from HmComment where Id in (" + ids + ")", conn);
            if (Connect())
            {
                cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        public Comment Read(int id)
        {
            Comment c = null;
            if (Connect())
            {
                OleDbCommand cmd = new OleDbCommand("select * from HmComment where ID=@id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    c = new Comment();
                    c.Id = dr.GetInt32(0);
                    c.PostId = dr.GetInt32(1);
                    c.Author = dr.GetString(2);
                    c.AuthorEmail = dr.GetString(3);
                    c.CommentDate = dr.GetDateTime(4);
                    c.CommentContent = dr.GetString(5);
                    c.CommentType = dr.GetString(6);
                    c.Approved = dr.GetBoolean(7);
                    c.ParentId = dr.GetInt32(8);
                    c.UserId = dr.GetInt32(9);
                }
                Disconnect();
            }
            return c;
        }
        public List<Comment> ReadMany(int pageSize, int pageNum,int postId, string text, string authorEmail,bool? approved, DateTime? fromDate,DateTime? toDate, out int total)
        {
            List<Comment> list = new List<Comment>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmComment", conn);
            total = 0;
            if (Connect())
            {
                 total = (int)cmd.ExecuteScalar();
                QueryCommandBuilder qb = new QueryCommandBuilder("select TOP #pageSize * from (select TOP #total * from HmComment where 1=1");
                qb.AddSuffix("order by Id asc) order by Id desc");
                qb.AddParam("pageSize", pageSize);
                int tmp = total - (pageNum - 1) * pageSize;
                qb.AddParam("total", tmp > 0 ? tmp : 1);
                
                if (postId > 0) qb.AddWithValue("PostId", postId);
                if (!String.IsNullOrEmpty(text)) qb.AddWithValue("CommentContent", text,"like");
                if (!String.IsNullOrEmpty(authorEmail)) qb.AddWithValue("AuthorEmail", authorEmail, "like");
                if (approved!=null) qb.AddWithValue("ActivationKey", approved.Value);
                if (fromDate != null && toDate != null)
                {
                    qb.AddWithValue("CommentDate", fromDate,">=");
                    qb.AddWithValue("CommentDate", toDate,"<=");
                }
                qb.AddWithValue("Approved", approved);
                cmd = qb.GetBuildedCommand(conn);

                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                 Comment   c = new Comment();
                    c.Id = dr.GetInt32(0);
                    c.PostId = dr.GetInt32(1);
                    c.Author = dr.GetString(2);
                    c.AuthorEmail = dr.GetString(3);
                    c.CommentDate = dr.GetDateTime(4);
                    c.CommentContent = dr.GetString(5);
                    c.CommentType = dr.GetString(6);
                    c.Approved = dr.GetBoolean(7);
                    c.ParentId = dr.GetInt32(8);
                    c.UserId = dr.GetInt32(9);
                    list.Add(c);
                }
            }
            return list;
        }
    }
}